package jwr.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;



/*import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;*/


//import java.sql.*;

public class J20_BookdbDAO implements J20_BookdbService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String INSERTSQL="insert into book(bookid,title, publisher, author) values(bookid.nextval,?,?,?)";
	private static final String UPDATESQL="update book set title = ?, publisher = ?, author = ?, regdate = sysdate where bookid = ?";
	private static final String DELETESQL="delete from book where bookid = ?";
	private static final String SELECTSQL="select * from book order by bookid desc";
	private static final String SELECTONESQL="select * from book where bookid = ?";
	@Override
	public int insertBook(J20_BookdbVO bvo) {
		int cnt=0;
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(INSERTSQL);
			ps.setString(1, bvo.getTitle());
			ps.setString(2, bvo.getPublisher());
			ps.setString(3, bvo.getAuthor());
			cnt = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null)try{ps.close();} catch(Exception e){}
			if(conn!=null)try{conn.close();} catch(Exception e){}
		}
		return cnt;
	}

	@Override
	public String selectBook() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		String result="";
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SELECTSQL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i =1;i<=5;i++){
					result += rs.getString(i)+" ";
				}
				result+="\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();} catch (Exception e2) {	}
			if(ps!=null) try{ps.close();} catch (Exception e2) {	}
			if(conn!=null) try{conn.close();} catch (Exception e2) {	}
		}
		
		return result;
	}

	@Override
	public String selectBook(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SELECTONESQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				for(int i =1;i<=5;i++){
					result += rs.getString(i)+" ";
				}
				result+="\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();} catch (Exception e2) {	}
			if(ps!=null) try{ps.close();} catch (Exception e2) {	}
			if(conn!=null) try{conn.close();} catch (Exception e2) {	}
		}
		return result;
	}

	@Override
	public int updateBook(J20_BookdbVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(UPDATESQL);
			ps.setString(1, bvo.getTitle());
			ps.setString(2, bvo.getPublisher());
			ps.setString(3, bvo.getAuthor());
			ps.setString(4, bvo.getId());
			cnt = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null)try{ps.close();} catch(Exception e){e.getMessage();}
			if(conn!=null)try{conn.close();} catch(Exception e){e.getMessage();}
		}
		return cnt;
	}

	@Override
	public int deleteBook(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(DELETESQL);
			ps.setString(1, id);
			cnt = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ps.close();} catch(Exception e){}
			if(conn!=null) try{conn.close();} catch(Exception e){}
			
		}
		
		return cnt;
	}

}
